using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;

using SIS.ENT;


namespace SIS.DAL
{
    /// <summary>
    /// The TeacherProfileTableDB class is responsible for interacting with the database to retrieve and store information
    /// about TeacherProfileTable objects.
    /// </summary>
    public class TeacherProfileTableDB
    {
        #region "Public Methods"

        /// <summary>
        /// Gets an instance of TeacherProfileTable from the underlying datasource.
        /// </summary>
        /// <param name="teacherProfileKey">The unique TeacherProfileKey of the TeacherProfileTable in the database.</param>
        /// <returns>An TeacherProfileTable when the TeacherProfileKey was found in the database, or null otherwise.</returns>
        public static TeacherProfileTable GetItem(int teacherProfileKey, int SchoolKey)
        {
            TeacherProfileTable myTeacherProfileTable = null;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@teacherProfileKey", teacherProfileKey);
                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.Read())
                        {
                            myTeacherProfileTable = FillDataRecord(myReader);
                        }
                        myReader.Close();
                    }
                    myConnection.Close();
                }
                return myTeacherProfileTable;
            }
        }

        public static TeacherProfileTable GetItemByUserAccountKey(int UserAccountKey, int SchoolKey)
        {
            TeacherProfileTable myTeacherProfileTable = null;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectSingleItemByUserAccountKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@UserAccountKey", UserAccountKey);
                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.Read())
                        {
                            myTeacherProfileTable = FillDataRecord(myReader);
                        }
                        myReader.Close();
                    }
                    myConnection.Close();
                }
                return myTeacherProfileTable;
            }
        }

        /// <summary>
        /// Returns a list with TeacherProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the TeacherProfileTable objects.</returns>
        public static TeacherProfileTableList GetList(int SchoolKey)
        {
            TeacherProfileTableList tempList = new TeacherProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectList", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@schoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
            }
            return tempList;
        }

        /// <summary>
        /// Returns a list with TeacherProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the TeacherProfileTable objects.</returns>
        public static TeacherProfileTableList GetList(int SchoolKey, string Name, String Address, String NRICNO, Int16 AddressType, Int16 CountryCode, int CityCode, int DistrictCode)
        {
            TeacherProfileTableList tempList = new TeacherProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectListBySearch", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@schoolKey", SchoolKey);
                    myCommand.Parameters.AddWithValue("@NRICNumber", NRICNO);
                    myCommand.Parameters.AddWithValue("@Name", Name);
                    myCommand.Parameters.AddWithValue("@Address", Address);
                    myCommand.Parameters.AddWithValue("@AddressTypeCode", AddressType);
                    myCommand.Parameters.AddWithValue("@CountryCode", CountryCode);
                    myCommand.Parameters.AddWithValue("@CityCode", CityCode);
                    myCommand.Parameters.AddWithValue("@DistrictCode", DistrictCode);

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataSearchRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
            }
            return tempList;
        }


        /// <summary>
        /// Returns a list with TeacherProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the TeacherProfileTable objects.</returns>
        public static TeacherProfileTableList GetListByBloodGroupCode(short BloodGroupCode, int SchoolKey)
        {
            TeacherProfileTableList tempList = new TeacherProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectListByBloodGroupCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@bloodGroupCode", BloodGroupCode);
                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with TeacherProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the TeacherProfileTable objects.</returns>
        public static TeacherProfileTableList GetListByMartialStatusCode(short MartialStatusCode, int SchoolKey)
        {
            TeacherProfileTableList tempList = new TeacherProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectListByMartialStatusCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@martialStatusCode", MartialStatusCode);
                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with TeacherProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the TeacherProfileTable objects.</returns>
        public static TeacherProfileTableList GetListByNationalityCode(short NationalityCode, int SchoolKey)
        {
            TeacherProfileTableList tempList = new TeacherProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectListByNationalityCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@nationalityCode", NationalityCode);
                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with TeacherProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the TeacherProfileTable objects.</returns>
        public static TeacherProfileTableList GetListByRaceCode(short RaceCode, int SchoolKey)
        {
            TeacherProfileTableList tempList = new TeacherProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectListByRaceCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@raceCode", RaceCode);
                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with TeacherProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the TeacherProfileTable objects.</returns>
        public static TeacherProfileTableList GetListByReligionCode(short ReligionCode, int SchoolKey)
        {
            TeacherProfileTableList tempList = new TeacherProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectListByReligionCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@religionCode", ReligionCode);
                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with TeacherProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the TeacherProfileTable objects.</returns>
        public static TeacherProfileTableList GetListByUserAccountKey(int UserAccountKey, int SchoolKey)
        {
            TeacherProfileTableList tempList = new TeacherProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectListByUserAccountKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@userAccountKey", UserAccountKey);
                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Saves a TeacherProfileTable in the database.
        /// </summary>
        /// <param name="myTeacherProfileTable">The TeacherProfileTable instance to save.</param>
        /// <returns>The new TeacherProfileKey if the TeacherProfileTable is new in the database or the existing TeacherProfileKey when an item was updated.</returns>
        public static int Save(TeacherProfileTable myTeacherProfileTable)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableInsertUpdateSingleItem", myConnection))
                {
                    myConnection.Open();
                    myCommand.CommandType = CommandType.StoredProcedure;

                    if (myTeacherProfileTable.TeacherProfileKey == -1)
                    {
                        myCommand.Parameters.AddWithValue("@teacherProfileKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@teacherProfileKey", myTeacherProfileTable.TeacherProfileKey);
                    }
                    if (myTeacherProfileTable.UserAccountKey == -1)
                    {
                        myCommand.Parameters.AddWithValue("@userAccountKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@userAccountKey", myTeacherProfileTable.UserAccountKey);
                    }
                    if (string.IsNullOrEmpty(myTeacherProfileTable.FirstName))
                    {
                        myCommand.Parameters.AddWithValue("@firstName", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@firstName", myTeacherProfileTable.FirstName);
                    }
                    if (string.IsNullOrEmpty(myTeacherProfileTable.LastName))
                    {
                        myCommand.Parameters.AddWithValue("@lastName", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@lastName", myTeacherProfileTable.LastName);
                    }
                    if (string.IsNullOrEmpty(myTeacherProfileTable.OtherName))
                    {
                        myCommand.Parameters.AddWithValue("@otherName", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@otherName", myTeacherProfileTable.OtherName);
                    }
                    if (string.IsNullOrEmpty(myTeacherProfileTable.Gender))
                    {
                        myCommand.Parameters.AddWithValue("@gender", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@gender", myTeacherProfileTable.Gender);
                    }
                    if (myTeacherProfileTable.DateOfBirth == null)
                    {
                        myCommand.Parameters.AddWithValue("@dateOfBirth", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@dateOfBirth", myTeacherProfileTable.DateOfBirth);
                    }


                    if (myTeacherProfileTable.PlaceOfBirth == null)
                    {
                        myCommand.Parameters.AddWithValue("@placeOfBirth", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@placeOfBirth", myTeacherProfileTable.PlaceOfBirth);
                    }
                    if (myTeacherProfileTable.JoinDate == null)
                    {
                        myCommand.Parameters.AddWithValue("@JoinDate", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@JoinDate", myTeacherProfileTable.JoinDate);
                    }
                    if (myTeacherProfileTable.Hobby == null)
                    {
                        myCommand.Parameters.AddWithValue("@Hobby", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@Hobby", myTeacherProfileTable.Hobby);
                    }
                    if (myTeacherProfileTable.ParentStatus == null)
                    {
                        myCommand.Parameters.AddWithValue("@ParentStatus", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@ParentStatus", myTeacherProfileTable.ParentStatus);
                    }

                    if (myTeacherProfileTable.BloodGroupCode == null)
                    {
                        myCommand.Parameters.AddWithValue("@bloodGroupCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@bloodGroupCode", myTeacherProfileTable.BloodGroupCode);
                    }
                    if (myTeacherProfileTable.MartialStatusCode == null)
                    {
                        myCommand.Parameters.AddWithValue("@martialStatusCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@martialStatusCode", myTeacherProfileTable.MartialStatusCode);
                    }
                    if (myTeacherProfileTable.RaceCode == null)
                    {
                        myCommand.Parameters.AddWithValue("@raceCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@raceCode", myTeacherProfileTable.RaceCode);
                    }
                    if (myTeacherProfileTable.ReligionCode == null)
                    {
                        myCommand.Parameters.AddWithValue("@religionCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@religionCode", myTeacherProfileTable.ReligionCode);
                    }
                    if (myTeacherProfileTable.NationalityCode == null)
                    {
                        myCommand.Parameters.AddWithValue("@nationalityCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@nationalityCode", myTeacherProfileTable.NationalityCode);
                    }
                    if (string.IsNullOrEmpty(myTeacherProfileTable.NRICNumber))
                    {
                        myCommand.Parameters.AddWithValue("@nRICNumber", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@nRICNumber", myTeacherProfileTable.NRICNumber);
                    }
                    if (myTeacherProfileTable.DrivingLicenceType == null)
                    {
                        myCommand.Parameters.AddWithValue("@drivingLicenceType", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@drivingLicenceType", myTeacherProfileTable.DrivingLicenceType);
                    }
                    if (string.IsNullOrEmpty(myTeacherProfileTable.DrivingLicenseNumber))
                    {
                        myCommand.Parameters.AddWithValue("@drivingLicenseNumber", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@drivingLicenseNumber", myTeacherProfileTable.DrivingLicenseNumber);
                    }
                    if (myTeacherProfileTable.TravelDocumentType == null)
                    {
                        myCommand.Parameters.AddWithValue("@travelDocumentType", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@travelDocumentType", myTeacherProfileTable.TravelDocumentType);
                    }
                    if (string.IsNullOrEmpty(myTeacherProfileTable.TravelDocumentNumber))
                    {
                        myCommand.Parameters.AddWithValue("@travelDocumentNumber", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@travelDocumentNumber", myTeacherProfileTable.TravelDocumentNumber);
                    }
                    if (string.IsNullOrEmpty(myTeacherProfileTable.WebSiteUrl))
                    {
                        myCommand.Parameters.AddWithValue("@webSiteUrl", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@webSiteUrl", myTeacherProfileTable.WebSiteUrl);
                    }
                    if (myTeacherProfileTable.Status == null)
                    {
                        myCommand.Parameters.AddWithValue("@status", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@status", myTeacherProfileTable.Status);
                    }
                    if (myTeacherProfileTable.CreatedDateTime == null)
                    {
                        myCommand.Parameters.AddWithValue("@createdDateTime", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@createdDateTime", myTeacherProfileTable.CreatedDateTime);
                    }
                    if (myTeacherProfileTable.ModifiedDateTime == null)
                    {
                        myCommand.Parameters.AddWithValue("@modifiedDateTime", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@modifiedDateTime", myTeacherProfileTable.ModifiedDateTime);
                    }
                    

                    DbParameter returnValue;
                    returnValue = myCommand.CreateParameter();
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    myCommand.Parameters.Add(returnValue);

                    //myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    result = Convert.ToInt32(returnValue.Value);
                    myConnection.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// Deletes a TeacherProfileTable from the database.
        /// </summary>
        /// <param name="teacherProfileKey">The TeacherProfileKey of the TeacherProfileTable to delete.</param>
        /// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
        public static bool Delete(int teacherProfileKey)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableDeleteSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@teacherProfileKey", teacherProfileKey);
                    myConnection.Open();
                    result = myCommand.ExecuteNonQuery();

                    myConnection.Close();
                }
            }
            return result > 0;
        }

        #endregion

        /// <summary>
        /// Initializes a new instance of the TeacherProfileTable class and fills it with the data fom the IDataRecord.
        /// </summary>
        private static TeacherProfileTable FillDataRecord(IDataRecord myDataRecord)
        {
            TeacherProfileTable myTeacherProfileTable = new TeacherProfileTable();
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TeacherProfileKey")))
            {
                myTeacherProfileTable.TeacherProfileKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("TeacherProfileKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("UserAccountKey")))
            {
                myTeacherProfileTable.UserAccountKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("UserAccountKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FirstName")))
            {
                myTeacherProfileTable.FirstName = myDataRecord.GetString(myDataRecord.GetOrdinal("FirstName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LastName")))
            {
                myTeacherProfileTable.LastName = myDataRecord.GetString(myDataRecord.GetOrdinal("LastName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("OtherName")))
            {
                myTeacherProfileTable.OtherName = myDataRecord.GetString(myDataRecord.GetOrdinal("OtherName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Photo")))
            {
                myTeacherProfileTable.Photo = (byte[])myDataRecord.GetValue(myDataRecord.GetOrdinal("Photo"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Gender")))
            {
                myTeacherProfileTable.Gender = myDataRecord.GetString(myDataRecord.GetOrdinal("Gender"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DateOfBirth")))
            {
                myTeacherProfileTable.DateOfBirth = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("DateOfBirth"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("PlaceOfBirth")))
            {
                myTeacherProfileTable.PlaceOfBirth = myDataRecord.GetString(myDataRecord.GetOrdinal("PlaceOfBirth"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("JoinDate")))
            {
                myTeacherProfileTable.JoinDate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("JoinDate"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ParentStatus")))
            {
                myTeacherProfileTable.ParentStatus = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ParentStatus"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Hobby")))
            {
                myTeacherProfileTable.Hobby = myDataRecord.GetString(myDataRecord.GetOrdinal("Hobby"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("BloodGroupCode")))
            {
                myTeacherProfileTable.BloodGroupCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("BloodGroupCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("MartialStatusCode")))
            {
                myTeacherProfileTable.MartialStatusCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("MartialStatusCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RaceCode")))
            {
                myTeacherProfileTable.RaceCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("RaceCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ReligionCode")))
            {
                myTeacherProfileTable.ReligionCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ReligionCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NationalityCode")))
            {
                myTeacherProfileTable.NationalityCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("NationalityCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NRICNumber")))
            {
                myTeacherProfileTable.NRICNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("NRICNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenceType")))
            {
                myTeacherProfileTable.DrivingLicenceType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("DrivingLicenceType"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenseNumber")))
            {
                myTeacherProfileTable.DrivingLicenseNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("DrivingLicenseNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentType")))
            {
                myTeacherProfileTable.TravelDocumentType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("TravelDocumentType"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentNumber")))
            {
                myTeacherProfileTable.TravelDocumentNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("TravelDocumentNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("WebSiteUrl")))
            {
                myTeacherProfileTable.WebSiteUrl = myDataRecord.GetString(myDataRecord.GetOrdinal("WebSiteUrl"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
            {
                myTeacherProfileTable.Status = myDataRecord.GetInt16(myDataRecord.GetOrdinal("Status"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreatedDateTime")))
            {
                myTeacherProfileTable.CreatedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedDateTime"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
            {
                myTeacherProfileTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
            }
            
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Teacher")))
            {
                myTeacherProfileTable.Teacher = myDataRecord.GetString(myDataRecord.GetOrdinal("Teacher"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("SchoolKey")))
            {
                myTeacherProfileTable.SchoolKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("SchoolKey"));
            }
            return myTeacherProfileTable;
        }

        private static TeacherProfileTable FillDataSearchRecord(IDataRecord myDataRecord)
        {
            TeacherProfileTable myTeacherProfileTable = new TeacherProfileTable();
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TeacherProfileKey")))
            {
                myTeacherProfileTable.TeacherProfileKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("TeacherProfileKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("UserAccountKey")))
            {
                myTeacherProfileTable.UserAccountKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("UserAccountKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FirstName")))
            {
                myTeacherProfileTable.FirstName = myDataRecord.GetString(myDataRecord.GetOrdinal("FirstName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LastName")))
            {
                myTeacherProfileTable.LastName = myDataRecord.GetString(myDataRecord.GetOrdinal("LastName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("OtherName")))
            {
                myTeacherProfileTable.OtherName = myDataRecord.GetString(myDataRecord.GetOrdinal("OtherName"));
            }
            
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Gender")))
            {
                myTeacherProfileTable.Gender = myDataRecord.GetString(myDataRecord.GetOrdinal("Gender"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DateOfBirth")))
            {
                myTeacherProfileTable.DateOfBirth = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("DateOfBirth"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("PlaceOfBirth")))
            {
                myTeacherProfileTable.PlaceOfBirth = myDataRecord.GetString(myDataRecord.GetOrdinal("PlaceOfBirth"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("JoinDate")))
            {
                myTeacherProfileTable.JoinDate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("JoinDate"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ParentStatus")))
            {
                myTeacherProfileTable.ParentStatus = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ParentStatus"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Hobby")))
            {
                myTeacherProfileTable.Hobby = myDataRecord.GetString(myDataRecord.GetOrdinal("Hobby"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("BloodGroupCode")))
            {
                myTeacherProfileTable.BloodGroupCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("BloodGroupCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("MartialStatusCode")))
            {
                myTeacherProfileTable.MartialStatusCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("MartialStatusCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RaceCode")))
            {
                myTeacherProfileTable.RaceCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("RaceCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ReligionCode")))
            {
                myTeacherProfileTable.ReligionCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ReligionCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NationalityCode")))
            {
                myTeacherProfileTable.NationalityCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("NationalityCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NRICNumber")))
            {
                myTeacherProfileTable.NRICNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("NRICNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenceType")))
            {
                myTeacherProfileTable.DrivingLicenceType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("DrivingLicenceType"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenseNumber")))
            {
                myTeacherProfileTable.DrivingLicenseNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("DrivingLicenseNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentType")))
            {
                myTeacherProfileTable.TravelDocumentType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("TravelDocumentType"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentNumber")))
            {
                myTeacherProfileTable.TravelDocumentNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("TravelDocumentNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("WebSiteUrl")))
            {
                myTeacherProfileTable.WebSiteUrl = myDataRecord.GetString(myDataRecord.GetOrdinal("WebSiteUrl"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
            {
                myTeacherProfileTable.Status = myDataRecord.GetInt16(myDataRecord.GetOrdinal("Status"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreatedDateTime")))
            {
                myTeacherProfileTable.CreatedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedDateTime"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
            {
                myTeacherProfileTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
            }
            
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Teacher")))
            {
                myTeacherProfileTable.Teacher = myDataRecord.GetString(myDataRecord.GetOrdinal("Teacher"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("SchoolKey")))
            {
                myTeacherProfileTable.SchoolKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("SchoolKey"));
            }
            return myTeacherProfileTable;
        }
        /// <summary>
        /// Deletes a Teacher ProfileTable from the database.
        /// </summary>
        /// <param name="schoolKey">The SchoolKey of the SchoolProfileTable to delete.</param>
        /// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
        public static bool UpdatePhoto(int TeacherProfileKey, Byte[] imgPhoto)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableUpdatePhotoSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@TeacherProfileKey", TeacherProfileKey);
                    myCommand.Parameters.AddWithValue("@Photo", imgPhoto);
                    myConnection.Open();
                    result = myCommand.ExecuteNonQuery();
                    myConnection.Close();
                }
            }
            return result > 0;
        }


        /// <summary>
        /// Get Photo a TeacherProfileTable from the database.
        /// </summary>
        /// <param name="schoolKey">The SchoolKey of the SchoolProfileTable to delete.</param>
        /// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
        public static Byte[] GetPhoto(int TeacherProfileKey)
        {
            Byte[] imgPhoto = null;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocTeacherProfileTableSelectPhotoSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@TeacherProfileKey", TeacherProfileKey);
                    myConnection.Open();
                    myCommand.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                imgPhoto = (Byte[])myReader["Photo"];
                            }
                        }
                        myReader.Close();
                    }
                    myConnection.Close();
                }
            }
            return imgPhoto;
        }
    }



}
